/**   
* @Title: Snippet.java 
* @Package com.txby.kettle.utils 
* @Description: TODO(用一句话描述该文件做什么) 
* @author jacking  
* @date 2018年3月20日 上午8:36:31 
* @version V1.0   
*/
package com.jck.data.util;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.Test;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.database.Database;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.util.EnvUtil;

import com.alibaba.fastjson.JSON;

/**
 * kettle独立数据源执行和查询sql处理
* @ClassName: KettleExecBySqlUtil 
* @Description: TODO(这里用一句话描述这个类的作用) 
* @author jacking
* @date 2018年3月20日 上午8:41:49 
*
 */
public class KettleExecBySqlUtil {
	/**
	 * 根据自定义sql及数据源信息查询数据
	* @Title: findBySql 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param kds
	* @param @param customSql
	* @param @return
	* @param @throws Exception    设定文件 
	* @return List<Map>    返回类型 
	* @throws
	 */
	public static  List<Map> findBySql(KettleDataSource kds,String customSql) throws Exception{
		//初始化
		KettleEnvironment.init();
		EnvUtil.environmentInit(); 
				String[] databasesXML = {
					"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
				            "<connection>" +
				            "<name>"+kds.getrName()+"</name>" +
				            "<server>"+kds.getrServer()+"</server>" +
				            "<type>"+kds.getrType()+"</type>" +
				            "<access>"+kds.getrAccess()+"</access>" + 
				            "<database>"+kds.getrDatabase()+"</database>" +
				            "<port>"+kds.getrPort()+"</port>" +
				            "<username>"+kds.getrUsername()+"</username>" +
				            "<password>"+kds.getrPassword()+"</password>" +
				          "</connection>"
				};
				DatabaseMeta databaseMeta = null;
				 try {
					 databaseMeta = new DatabaseMeta(databasesXML[0]);
				} catch (Exception e) {		
					e.printStackTrace();
				} 
				Database database =   new Database(null,databaseMeta);
				database.connect();
				ResultSet resultSet = database.openQuery(customSql);
	            List  list =ResultSetToList(resultSet);
	            resultSet.close();
	            database.disconnect();
				return list;
			}
	
	/**
	 * 根据自定义sql及数据源信息执行sql(如delete,drop)
	* @Title: execSql 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param databaseMeta
	* @param @param customSql
	* @param @throws Exception    设定文件 
	* @return void    返回类型 
	* @throws
	 */
	public static  void execSql(DatabaseMeta databaseMeta,String customSql) throws Exception{
		//初始化
		KettleEnvironment.init();
		EnvUtil.environmentInit(); 
		Database  database = new Database(null,databaseMeta);
		database.connect();
		database.execStatements(customSql);
		database.disconnect();
	}
	/**
	 * 测试数据源是否可用
	* @Title: TestConnecton 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param type mysql|sqlserver|oracle
	* @param @param host 127.0.0.1
	* @param @param port 3306
	* @param @param db testDB
	* @param @param user user
	* @param @param pass 123456
	* @param @return
	* @param @throws Exception    设定文件 
	* @return boolean    返回类型 
	* @throws
	 */
	public static  boolean TestConnecton(String type,String host,  String port,String db, String user,
		    String pass ) throws Exception{
		KettleEnvironment.init();
		type=type.toLowerCase().equals("sqlserver")?"mssql":type;
		DatabaseMeta databaseMeta=new DatabaseMeta(db,type,"jdbc",host,db,port,user,pass);
		String result=databaseMeta.testConnection();
		return result.contains("正确");
	}
	/**
	 * 根据自定义sql及数据源信息查询数据
	* @Title: findBySql 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param databaseMeta
	* @param @param customSql
	* @param @return
	* @param @throws Exception    设定文件 
	* @return List<Map>    返回类型 
	* @throws
	 */
	public static  List<Map> findBySql(DatabaseMeta databaseMeta,String customSql) throws Exception{
		Database  database = new Database(null,databaseMeta);
		database.connect();
		ResultSet resultSet  = database.openQuery(customSql);
	/*	List  list = new ArrayList ();
		while(resultSet.next()){
			String a = resultSet.getString("dbName");
			//           		System.out.println("表名"+a);
			list.add(a);
		}*/
		List list=ResultSetToList(resultSet);
		 resultSet.close();
         database.disconnect();
		return list;
	}
	
	public static List<Map<String, Object>> ResultSetToList(ResultSet rs) throws SQLException {
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		ResultSetMetaData rsmd = rs.getMetaData();
		int colCount = rsmd.getColumnCount();
		List<String> colNameList = new ArrayList<String>();
		for (int i = 0; i < colCount; i++) {
			colNameList.add(rsmd.getColumnName(i + 1));
		}
		while (rs.next()) {
			Map map = new HashMap<String, Object>();
			for (int i = 0; i < colCount; i++) {
				String key = colNameList.get(i);
				Object value = rs.getString(colNameList.get(i));
				map.put(key, value);
			}
			results.add(map);
		}
		return results;
	}
	
	@Test
	public void tt() throws KettleException {
		KettleEnvironment.init();
		DatabaseMeta databaseMeta=new DatabaseMeta("THIS4","mssql","jdbc","192.168.2.72","THIS4","1433","user","123456");
		String result=databaseMeta.testConnection();
		System.out.println(result);
	}
	
	/*@Test
	public void test3() throws Exception{
		DatabaseMeta databaseMeta= KettleExecuUtil.getDatabaseMeta(null, "192.168.2.71", "mysql", "3306", "txby-hisjob", "user", "123456");
		String customSql="select * from t_txby_jobDatasource";
		List l=KettleExecBySqlUtil.findBySql(databaseMeta, customSql);
		System.out.println(l);
		
	}
	@Test
	public void test() throws Exception{
		DatabaseMeta databaseMeta= KettleExecuUtil.getDatabaseMeta(null, "192.168.2.71", "mysql", "3306", "txby-hisjob", "user", "123456");
		String customSql="select dbDriverClassName from t_txby_jobDatasource";
		List<Map> l=KettleExecBySqlUtil.findBySql(databaseMeta, customSql);
//		System.out.println(JSON.toJSONString(l));
		System.out.println(l.size());
		System.out.println(JSON.toJSONString(l));
		for(Map m:l){
			System.out.println(JSON.toJSONString(m));
		}
		
	}
	@Test
	public void test2() throws Exception{
		DatabaseMeta databaseMeta= KettleExecuUtil.getDatabaseMeta(null, "192.168.2.71", "mysql", "3306", "txby-hisjob", "user", "123456");
		String customSql="delete from t_txby_jobDatasource where id_=55";
		KettleExecBySqlUtil.execSql(databaseMeta, customSql);
		System.out.println("ok");
		
	}
	public static void main(String[] args) throws Exception {
		KettleDataSource kds=new KettleDataSource("pro-mysql-hisjob", "192.168.2.71", "mysql", "jdbc", "txby-hisjob", "3306", "user", "123456");
		String customSql="select dbDriverClassName from t_txby_jobDatasource";
		List l=KettleExecBySqlUtil.findBySql(kds, customSql);
		System.out.println(JSON.toJSONString(l));
	}*/
	/*public static void main(String[] args) throws Exception {
		System.out.println(TestConnecton("mysql","192.168.2.71", "3306", "txby-hisjob","user", "123456"));
		KettleDataSource kds=new KettleDataSource(null, "192.168.2.71", "mysql", "jdbc", "txby-hisjob", "3306", "user", "123456");
		String customSql="select dbDriverClassName from t_txby_jobDatasource";
		List l=KettleExecBySqlUtil.findBySql(kds, customSql);
		System.out.println(JSON.toJSONString(l));
		 
	}*/
}

